﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using LibraryO2S;

public partial class Administrator_ReportRevenue : System.Web.UI.Page
{
    Dataprocess data = new Dataprocess();
    protected void Page_Load(object sender, EventArgs e)
    {
      
        if (!IsPostBack)
        {

            txtdate.Text = "01/09/2013";// DateTime.Now.Day.ToString("00") + "/" + DateTime.Now.Month.ToString("00") + "/" + DateTime.Now.Year.ToString("0000");
            txtdate1.Text = "01/09/2013";// DateTime.Now.Day.ToString("00") + "/" + DateTime.Now.Month.ToString("00") + "/" + DateTime.Now.Year.ToString("0000");
            Bindata();

            ddlloaithe.Items.FindByText("").Selected = true;
            ddlnhomsp.Items.FindByText("").Selected = true;
            ddlnv.Items.FindByText("").Selected = true;



            string date = txtdate.Text.Substring(3, 2) + "/" + txtdate.Text.Substring(0, 2) + "/" + txtdate.Text.Substring(6, 4) + " 00:00";
            string date1 = txtdate1.Text.Substring(3, 2) + "/" + txtdate1.Text.Substring(0, 2) + "/" + txtdate1.Text.Substring(6, 4) + " 23:59";
            string cardtype = ddlloaithe.SelectedValue;
            string cateproduct = ddlnhomsp.SelectedValue;
            string usercode = ddlnv.SelectedValue;




            rptthongke.DataSource = LoadTotalCardProduct(date, date1, cardtype, cateproduct, usercode);
            rptthongke.DataBind();

            CollectionPager1.DataSource = LoadCardProduct(date, date1, cardtype, cateproduct, usercode).DefaultView;
            CollectionPager1.PageSize = 50;
            CollectionPager1.BindToControl = rptData;
            rptData.DataSource = CollectionPager1.DataSourcePaged;
            rptData.DataBind();


        }
        else
        {

            Response.Write("<script>alert('oke')</script>");



        }
    }

    public void Bindata()
    {

        ddlnhomsp.DataSource = data.allSystem("select * from tblProductCategory ");
        ddlnhomsp.DataTextField = "Name";
        ddlnhomsp.DataValueField = "Id";
        ddlnhomsp.DataBind();

        ddlnv.DataSource = data.allSystem("select * from tblUser");
        ddlnv.DataTextField = "FullName";
        ddlnv.DataValueField = "Code";
        ddlnv.DataBind();


        ddlloaithe.DataSource = data.allSystem("select * from tblCardType");
        ddlloaithe.DataTextField = "Name";
        ddlloaithe.DataValueField = "Code";
        ddlloaithe.DataBind();



        ddlloaithe.Items.Add("");
        ddlnhomsp.Items.Add("");
        ddlnv.Items.Add("");
    }


    private DataTable LoadCardProduct(string date, string date1, string cardtype, string cateproduct, string usercode)
    {

        DataTable table = new DataTable();
        DataColumn dtColumn = new DataColumn("date");
        table.Columns.Add(dtColumn);
        DataColumn dtColumn1 = new DataColumn("mathesp");
        table.Columns.Add(dtColumn1);
        DataColumn dtColumn2 = new DataColumn("loaithesp");
        table.Columns.Add(dtColumn2);
        DataColumn dtColumn3 = new DataColumn("sl");
        table.Columns.Add(dtColumn3);
        DataColumn dtColumn4 = new DataColumn("tien");
        table.Columns.Add(dtColumn4);
        DataColumn dtColumn5 = new DataColumn("nvbanhang");
        table.Columns.Add(dtColumn5);


        DataTable dbrevenue = data.allSystem("select dbo.tblRegisterSwim.Date, dbo.tblRegisterSwim.CardNumber as mathe, dbo.tblRegisterSwim.PayMoney, dbo.tblUser.FullName as nhanvien, tblCardType.Name from tblRegisterSwim LEFT JOIN tblCard ON dbo.tblRegisterSwim.CardNumber=dbo.tblCard.CardNumber LEFT JOIN tblCardType ON dbo.tblCard.CardTypeCode=dbo.tblCardType.Code LEFT JOIN tblUser ON dbo.tblRegisterSwim.UserCode=dbo.tblUser.Code where  dbo.tblRegisterSwim.IsDelete=0 and dbo.tblRegisterSwim.Date>='" + date + "' and dbo.tblRegisterSwim.Date<='" + date1 + "' and tblCardType.Code like '%" + cardtype + "%' and tblRegisterSwim.UserCode like '%" + usercode + "%' order by dbo.tblRegisterSwim.Date desc  ");
        foreach (DataRow dr in dbrevenue.Rows)
        {
            DataRow dtRow = table.NewRow();

            dtRow[0] = dr["Date"].ToString();
            dtRow[1] = dr["mathe"].ToString();
            dtRow[2] = dr["Name"].ToString();
            dtRow[3] = 1;
            dtRow[4] = dr["PayMoney"].ToString();

            dtRow[5] = dr["nhanvien"].ToString();
            table.Rows.Add(dtRow);

        }
        DataTable dbpro = data.allSystem("select dbo.tblImExport.Id, dbo.tblImExport.Date, dbo.tblProduct.Code, dbo.tblProduct.Name, dbo.tblProduct.ProductCategoryID, tblImExport.Quantity, tblImExport.UnitPrice, dbo.tblUser.FullName from  tblImExport LEFT JOIN tblProduct ON dbo.tblImExport.ProductCode=dbo.tblProduct.Code LEFT JOIN tblUser ON dbo.tblImExport.UserCode=dbo.tblUser.Code where Action='EXP' and dbo.tblImExport.Date>='" + date + "' and  dbo.tblImExport.Date<='" + date1 + "' and dbo.tblProduct.ProductCategoryID like '%" + cateproduct + "%' and dbo.tblImExport.UserCode like '%" + usercode + "%' order by dbo.tblImExport.Date desc  ");
        foreach (DataRow drpro in dbpro.Rows)
        {
            DataRow dtRow1 = table.NewRow();

            dtRow1[0] = drpro["Date"].ToString();
            dtRow1[1] = drpro["Code"].ToString();
            dtRow1[2] = drpro["Name"].ToString();
            dtRow1[3] = drpro["Quantity"].ToString(); ;
            dtRow1[4] = drpro["UnitPrice"].ToString();
            dtRow1[5] = drpro["FullName"].ToString();
            table.Rows.Add(dtRow1);

        }

        return table;
    }
    private DataTable LoadTotalCardProduct(string date, string date1, string cardtype, string cateproduct, string usercode)
    {
        long tiencard = 0;
        long tiensp = 0;

        DataTable table = new DataTable();
        DataColumn dtColumn = new DataColumn("nhomthe");
        table.Columns.Add(dtColumn);
        DataColumn dtColumn1 = new DataColumn("Sl");
        table.Columns.Add(dtColumn1);
        DataColumn dtColumn2 = new DataColumn("tien");
        table.Columns.Add(dtColumn2);
        DataColumn dtColumn3 = new DataColumn("tonkho");
        table.Columns.Add(dtColumn3);


        DataTable dbcatecard = data.allSystem("select * from tblCardType order by Id ");
        foreach (DataRow drcatecard in dbcatecard.Rows)
        {
            long totalquantitycard = 0;
            long totalmomey = 0;

            string code = drcatecard["Code"].ToString();
            string cardtypename = drcatecard["Name"].ToString();
            DataTable db = data.allSystem("select dbo.tblRegisterSwim.Date, dbo.tblRegisterSwim.CardNumber, dbo.tblRegisterSwim.PayMoney, dbo.tblUser.FullName, tblCardType.Name from tblRegisterSwim LEFT JOIN tblCard ON dbo.tblRegisterSwim.CardNumber=dbo.tblCard.CardNumber LEFT JOIN tblCardType ON dbo.tblCard.CardTypeCode=dbo.tblCardType.Code LEFT JOIN tblUser ON dbo.tblRegisterSwim.UserCode=dbo.tblUser.Code where dbo.tblCardType.Code='" + code + "' and dbo.tblRegisterSwim.Date>='" + date + "' and dbo.tblRegisterSwim.IsDelete=0 and dbo.tblRegisterSwim.Date<='" + date1 + "' and tblCardType.Code like '%" + cardtype + "%' and tblRegisterSwim.UserCode like '%" + usercode + "%' order by dbo.tblRegisterSwim.Date desc  ");

            if (db != null && db.Rows.Count > 0)
            {

                foreach (DataRow drtotalcard in db.Rows)
                {
                    long price = Convert.ToInt64(drtotalcard["PayMoney"].ToString());

                    totalquantitycard += 1;
                    totalmomey += price;
                }

                tiencard += totalmomey;
                DataRow dtRow = table.NewRow();

                dtRow[0] = cardtypename;
                dtRow[1] = totalquantitycard;
                dtRow[2] = String.Format("{0:0,0}", totalmomey);
                dtRow[3] = "#";
                table.Rows.Add(dtRow);


            }

        }


        DataTable dbcatename = data.allSystem("select * from tblProduct order by Id");
        foreach (DataRow drcate in dbcatename.Rows)
        {
            long quantity = 0;
            long price = 0;
            string ProductInStock = "";
            string codepro = drcate["Code"].ToString();
            string name = drcate["Name"].ToString();
            DataTable dbpro = data.allSystem(" select dbo.tblImExport.Id, dbo.tblImExport.Date, dbo.tblProduct.Code, dbo.tblProduct.Name, dbo.tblProduct.ProductCategoryID, tblImExport.Quantity, tblImExport.UnitPrice,tblProduct.ProductInStock, dbo.tblUser.FullName from  tblImExport LEFT JOIN tblProduct ON dbo.tblImExport.ProductCode=dbo.tblProduct.Code LEFT JOIN tblUser ON dbo.tblImExport.UserCode=dbo.tblUser.Code where Action='EXP' and dbo.tblProduct.Code=" + codepro + " and dbo.tblImExport.Date>='" + date + "' and dbo.tblImExport.Date<='" + date1 + "' and dbo.tblProduct.ProductCategoryID like '%" + cateproduct + "%' and dbo.tblImExport.UserCode like '%" + usercode + "%'  ");
            if (dbpro != null && dbpro.Rows.Count > 0)
            {

                foreach (DataRow dr in dbpro.Rows)
                {

                    long gia = Convert.ToInt64(dr["UnitPrice"].ToString());
                    long quan = Convert.ToInt64(dr["Quantity"].ToString());
                    ProductInStock = dr["ProductInStock"].ToString();
                    quantity += quan;
                    price += gia * quan;


                }

                tiensp += price;
                DataRow dtRow1 = table.NewRow();
                dtRow1[0] = name;
                dtRow1[1] = quantity;
                dtRow1[2] = String.Format("{0:0,0}", price);
                dtRow1[3] = ProductInStock;
                table.Rows.Add(dtRow1);

            }

        }

        long tong = tiencard + tiensp;
        lbltotalmoney.Text = String.Format("{0:0,0}", tong);

        return table;
    }

    protected void btnAdnew1_Click(object sender, EventArgs e)
    {
        Response.Redirect("BannerAdd.aspx");
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {

    }
    protected void btnUpdateDisplayOrder_Click(object sender, EventArgs e)
    {
        //LoadData();

    }
    protected void btnthongke_Click(object sender, EventArgs e)
    {

        Label1.Text = "  từ ngày " + txtdate.Text + " đến " + txtdate1.Text;

        string date = txtdate.Text.Substring(3, 2) + "/" + txtdate.Text.Substring(0, 2) + "/" + txtdate.Text.Substring(6, 4) + " 00:00";
        string date1 = txtdate1.Text.Substring(3, 2) + "/" + txtdate1.Text.Substring(0, 2) + "/" + txtdate1.Text.Substring(6, 4) + " 23:59";
        string cardtype = ddlloaithe.SelectedValue;
        string cateproduct = ddlnhomsp.SelectedValue;
        string usercode = ddlnv.SelectedValue;




        rptthongke.DataSource = LoadTotalCardProduct(date, date1, cardtype, cateproduct, usercode);
        rptthongke.DataBind();

        CollectionPager1.DataSource = LoadCardProduct(date, date1, cardtype, cateproduct, usercode).DefaultView;
        CollectionPager1.PageSize = 50;
        CollectionPager1.BindToControl = rptData;
        rptData.DataSource = CollectionPager1.DataSourcePaged;
        rptData.DataBind();


    }
    private DataTable LoadallCardProduct()
    {

        DataTable table = new DataTable();
        DataColumn dtColumn = new DataColumn("date");
        table.Columns.Add(dtColumn);
        DataColumn dtColumn1 = new DataColumn("mathesp");
        table.Columns.Add(dtColumn1);
        DataColumn dtColumn2 = new DataColumn("loaithesp");
        table.Columns.Add(dtColumn2);
        DataColumn dtColumn3 = new DataColumn("sl");
        table.Columns.Add(dtColumn3);
        DataColumn dtColumn4 = new DataColumn("tien");
        table.Columns.Add(dtColumn4);
        DataColumn dtColumn5 = new DataColumn("nvbanhang");
        table.Columns.Add(dtColumn5);


        DataTable dbrevenue = data.allSystem("select dbo.tblRegisterSwim.Date, dbo.tblRegisterSwim.CardNumber as mathe, dbo.tblRegisterSwim.PayMoney, dbo.tblUser.FullName as nhanvien, tblCardType.Name from tblRegisterSwim LEFT JOIN tblCard ON dbo.tblRegisterSwim.CardNumber=dbo.tblCard.CardNumber LEFT JOIN tblCardType ON dbo.tblCard.CardTypeCode=dbo.tblCardType.Code LEFT JOIN tblUser ON dbo.tblRegisterSwim.UserCode=dbo.tblUser.Code where  dbo.tblRegisterSwim.IsDelete=0  order by dbo.tblRegisterSwim.Date desc  ");
        foreach (DataRow dr in dbrevenue.Rows)
        {
            DataRow dtRow = table.NewRow();

            dtRow[0] = dr["Date"].ToString();
            dtRow[1] = dr["mathe"].ToString();
            dtRow[2] = dr["Name"].ToString();
            dtRow[3] = 1;
            dtRow[4] = dr["PayMoney"].ToString();

            dtRow[5] = dr["nhanvien"].ToString();
            table.Rows.Add(dtRow);

        }
        DataTable dbpro = data.allSystem("select dbo.tblImExport.Id, dbo.tblImExport.Date, dbo.tblProduct.Code, dbo.tblProduct.Name, dbo.tblProduct.ProductCategoryID, tblImExport.Quantity, tblImExport.UnitPrice, dbo.tblUser.FullName from  tblImExport LEFT JOIN tblProduct ON dbo.tblImExport.ProductCode=dbo.tblProduct.Code LEFT JOIN tblUser ON dbo.tblImExport.UserCode=dbo.tblUser.Code where Action='EXP'  order by dbo.tblImExport.Date desc  ");
        foreach (DataRow drpro in dbpro.Rows)
        {
            DataRow dtRow1 = table.NewRow();

            dtRow1[0] = drpro["Date"].ToString();
            dtRow1[1] = drpro["Code"].ToString();
            dtRow1[2] = drpro["Name"].ToString();
            dtRow1[3] = drpro["Quantity"].ToString(); ;
            dtRow1[4] = drpro["UnitPrice"].ToString();
            dtRow1[5] = drpro["FullName"].ToString();
            table.Rows.Add(dtRow1);

        }

        return table;
    }
    private DataTable LoadallTotalCardProduct()
    {
        long tiencard = 0;
        long tiensp = 0;

        DataTable table = new DataTable();
        DataColumn dtColumn = new DataColumn("nhomthe");
        table.Columns.Add(dtColumn);
        DataColumn dtColumn1 = new DataColumn("Sl");
        table.Columns.Add(dtColumn1);
        DataColumn dtColumn2 = new DataColumn("tien");
        table.Columns.Add(dtColumn2);
        DataColumn dtColumn3 = new DataColumn("tonkho");
        table.Columns.Add(dtColumn3);


        DataTable dbcatecard = data.allSystem("select * from tblCardType order by Id ");
        foreach (DataRow drcatecard in dbcatecard.Rows)
        {
            long totalquantitycard = 0;
            long totalmomey = 0;

            string code = drcatecard["Code"].ToString();
            string cardtypename = drcatecard["Name"].ToString();
            DataTable db = data.allSystem("select dbo.tblRegisterSwim.Date, dbo.tblRegisterSwim.CardNumber, dbo.tblRegisterSwim.PayMoney, dbo.tblUser.FullName, tblCardType.Name from tblRegisterSwim LEFT JOIN tblCard ON dbo.tblRegisterSwim.CardNumber=dbo.tblCard.CardNumber LEFT JOIN tblCardType ON dbo.tblCard.CardTypeCode=dbo.tblCardType.Code LEFT JOIN tblUser ON dbo.tblRegisterSwim.UserCode=dbo.tblUser.Code order by dbo.tblRegisterSwim.Date desc  ");

            if (db != null && db.Rows.Count > 0)
            {

                foreach (DataRow drtotalcard in db.Rows)
                {
                    long price = Convert.ToInt64(drtotalcard["PayMoney"].ToString());

                    totalquantitycard += 1;
                    totalmomey += price;
                }

                tiencard += totalmomey;
                DataRow dtRow = table.NewRow();

                dtRow[0] = cardtypename;
                dtRow[1] = totalquantitycard;
                dtRow[2] = String.Format("{0:0,0}", totalmomey);
                dtRow[3] = "#";
                table.Rows.Add(dtRow);


            }

        }


        DataTable dbcatename = data.allSystem("select * from tblProduct order by Id");
        foreach (DataRow drcate in dbcatename.Rows)
        {
            long quantity = 0;
            long price = 0;
            string ProductInStock = "";
            string codepro = drcate["Code"].ToString();
            string name = drcate["Name"].ToString();
            DataTable dbpro = data.allSystem(" select dbo.tblImExport.Id, dbo.tblImExport.Date, dbo.tblProduct.Code, dbo.tblProduct.Name, dbo.tblProduct.ProductCategoryID, tblImExport.Quantity, tblImExport.UnitPrice,tblProduct.ProductInStock, dbo.tblUser.FullName from  tblImExport LEFT JOIN tblProduct ON dbo.tblImExport.ProductCode=dbo.tblProduct.Code LEFT JOIN tblUser ON dbo.tblImExport.UserCode=dbo.tblUser.Code where Action='EXP'  ");
            if (dbpro != null && dbpro.Rows.Count > 0)
            {

                foreach (DataRow dr in dbpro.Rows)
                {

                    long gia = Convert.ToInt64(dr["UnitPrice"].ToString());
                    long quan = Convert.ToInt64(dr["Quantity"].ToString());
                    ProductInStock = dr["ProductInStock"].ToString();
                    quantity += quan;
                    price += gia * quan;


                }

                tiensp += price;
                DataRow dtRow1 = table.NewRow();
                dtRow1[0] = name;
                dtRow1[1] = quantity;
                dtRow1[2] = String.Format("{0:0,0}", price);
                dtRow1[3] = ProductInStock;
                table.Rows.Add(dtRow1);

            }

        }

        long tong = tiencard + tiensp;
        lbltotalmoney.Text = String.Format("{0:0,0}", tong);

        return table;
    }
}